import pymysql
import pandas as pd
import time

# -------------------------------- DB basic info------------------------------
mysql_ip = '172.16.122.68'
mysql_port = 3306
mysql_user = 'test'
mysql_password = 'dpi@2021'
mysql_database = 'zentao'

# mysql_ip = '127.0.0.1'
# mysql_port = 3306
# mysql_user = 'root'
# mysql_password = 'root'
# mysql_database = 'zentao'


# ----------------------------------------------------------------------------

pyconect = pymysql.connect(
    host=mysql_ip,
    port=mysql_port,
    user=mysql_user,
    password=mysql_password,
    db=mysql_database,
    charset='utf8'
)

pycursor = pyconect.cursor(pymysql.cursors.DictCursor)

sql = """
select 
		bug.id as "Bug编号",
		pd.`name` as "所属产品",
		if(md.`name` is not NULL,CONCAT(md.`name`,"(#",bug.module,")"), "(#0)") as "所属模块",
		CONCAT(CONCAT_WS("(#",CONCAT_WS("/",pre_id.name,suffix_id.name),bug.execution),")")  as "所属执行",
		if(sty.title is not NULL,sty.title, "(#0)") as "相关需求",
		tsk.name as "相关任务",
		bug.title as "Bug标题",
		bug.keywords as "关键词",
		bug.severity as "严重程度",
		bug.pri as "优先级",
		(SELECT
			case bug.type
			WHEN "codeerror" THEN "代码错误"
			WHEN "codeimprovement" THEN "已关闭"
			WHEN "config" THEN "配置相关"		
			WHEN "designdefect" THEN "设计缺陷"			
			WHEN "install" THEN "安装部署"			
			WHEN "others" THEN "其他"			
			WHEN "performance" THEN "性能问题"			
			WHEN "security" THEN "安全相关"			
			WHEN "standard" THEN "标准规范"	
		END)	 as "Bug类型",
		bug.os as "操作系统",
		bug.browser as "浏览器",
		bug.steps as "重现步骤",
		(select 
			case bug.`status`
			WHEN "closed" THEN "已关闭"
			WHEN "resolved" THEN "已解决"
			WHEN "active" THEN "激活"
		 END) as "Bug状态",
		bug.deadline as "截止日期",
		bug.activatedCount as "激活次数",
		bug.confirmed as "是否确认",
		bug.mailto as "抄送给",
		usrA.realname as "有谁创建",
		bug.openedDate as "创建日期",
		bug.openedBuild as "影响版本",
		usrB.realname as "指派给",
		usrC.realname as "解决者",
		(SELECT
			case bug.resolution
		WHEN "fixed" THEN
		 "已修复"
		WHEN "external" THEN
		 "外部原因"		 
		WHEN "postponed" THEN
		 "延期解决"
		WHEN "willnotfix" THEN
		 "不予修复" 
		WHEN "bydesign" THEN
		 "设计如此"		
		WHEN "duplicate" THEN
		 "重复提交"				
		WHEN "tostory" THEN
		 "待解决"				
		END)
		 as "解决方案",
		bug.resolvedBuild as "解决版本",
		bug.resolvedDate as "解决日期",
		usrD.realname as "有谁关闭",
		bug.closedDate as "关闭日期",
		bug.duplicateBug as "重复Bug", 
		bug.linkBug as "相关Bug",
		bug.case as "相关用例",
		usrE.realname as "最后修改者",
		bug.lastEditedDate as "修改日期",
		bug.feedbackBy as "反馈者",
		bug.notifyEmail as "通知邮箱",
		fl.pathname as "附件"
FROM
	zt_bug as bug
LEFT JOIN
	zt_product as pd 
on 
	pd.id=bug.product
LEFT JOIN
	zt_module as md 
on 
	md.id = bug.module
LEFT JOIN
	zt_project as pj 
on 
	pj.id = bug.project
LEFT JOIN
	zt_project as ppj 
on 
	ppj.id = bug.execution
LEFT JOIN 
	zt_story as sty 
on 
	sty.id=bug.story
LEFT JOIN 
	zt_task as tsk
on 
	tsk.id=bug.task
LEFT JOIN
	zt_file as fl
on 
	fl.objectID=bug.id 
LEFT JOIN
	zt_user as usrA 
on 
	usrA.account=bug.openedBy
LEFT JOIN
	zt_user as usrB
on 
	usrB.account=bug.assignedTo
LEFT JOIN
	zt_user as usrC
on 
	usrC.account=bug.resolvedBy
LEFT JOIN
	zt_user as usrD
on 
	usrD.account=bug.closedBy
LEFT JOIN
	zt_user as usrE
on 
	usrE.account=bug.lastEditedBy
LEFT JOIN
	zt_project as pre_id 
on 
	SUBSTRING_INDEX(SUBSTRING_INDEX(ppj.path,",",2),",",-1)=pre_id.id 
LEFT JOIN
	zt_project as suffix_id 
on 
	SUBSTRING_INDEX(SUBSTRING_INDEX(ppj.path,",",3),",",-1)=suffix_id.id 
WHERE
	fl.objectType="bug"
and 
        bug.deleted='0'
"""


pycursor.execute(sql)
result = pycursor.fetchall()

df = pd.DataFrame.from_dict(result)

df.to_excel(f"Bug_all_basic_data_{'_'.join(str(item) for item in tuple(time.localtime())[:-3])}.xlsx", sheet_name='Bug数据', index=True, index_label="序号")



